package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Order;
import org.csu.mypetstore.persistence.OrderDao;
import org.csu.mypetstore.util.JDBCUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.util.List;

public class OrderDaoImpl implements OrderDao {
    private JdbcTemplate template = new JdbcTemplate(JDBCUtils.getDataSource());
    @Override
    public List<Order> getOrdersByUsername(String username) {
        String sql = " SELECT\n" +
                "      BILLADDR1 AS billAddress1,\n" +
                "      BILLADDR2 AS billAddress2,\n" +
                "      BILLCITY,\n" +
                "      BILLCOUNTRY,\n" +
                "      BILLSTATE,\n" +
                "      BILLTOFIRSTNAME,\n" +
                "      BILLTOLASTNAME,\n" +
                "      BILLZIP,\n" +
                "      SHIPADDR1 AS shipAddress1,\n" +
                "      SHIPADDR2 AS shipAddress2,\n" +
                "      SHIPCITY,\n" +
                "      SHIPCOUNTRY,\n" +
                "      SHIPSTATE,\n" +
                "      SHIPTOFIRSTNAME,\n" +
                "      SHIPTOLASTNAME,\n" +
                "      SHIPZIP,\n" +
                "      CARDTYPE,\n" +
                "      COURIER,\n" +
                "      CREDITCARD,\n" +
                "      EXPRDATE AS expiryDate,\n" +
                "      LOCALE,\n" +
                "      ORDERDATE,\n" +
                "      ORDERS.ORDERID,\n" +
                "      TOTALPRICE,\n" +
                "      USERID AS username,\n" +
                "      STATUS\n" +
                "    FROM ORDERS, ORDERSTATUS\n" +
                "    WHERE ORDERS.USERID = ?\n" +
                "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID\n" +
                "    ORDER BY ORDERDATE";
        return template.query(sql,new BeanPropertyRowMapper<Order>(Order.class),username);
    }

    @Override
    public Order getOrder(int orderId) {
        String sql = " select\n" +
                "      BILLADDR1 AS billAddress1,\n" +
                "      BILLADDR2 AS billAddress2,\n" +
                "      BILLCITY,\n" +
                "      BILLCOUNTRY,\n" +
                "      BILLSTATE,\n" +
                "      BILLTOFIRSTNAME,\n" +
                "      BILLTOLASTNAME,\n" +
                "      BILLZIP,\n" +
                "      SHIPADDR1 AS shipAddress1,\n" +
                "      SHIPADDR2 AS shipAddress2,\n" +
                "      SHIPCITY,\n" +
                "      SHIPCOUNTRY,\n" +
                "      SHIPSTATE,\n" +
                "      SHIPTOFIRSTNAME,\n" +
                "      SHIPTOLASTNAME,\n" +
                "      SHIPZIP,\n" +
                "      CARDTYPE,\n" +
                "      COURIER,\n" +
                "      CREDITCARD,\n" +
                "      EXPRDATE AS expiryDate,\n" +
                "      LOCALE,\n" +
                "      ORDERDATE,\n" +
                "      ORDERS.ORDERID,\n" +
                "      TOTALPRICE,\n" +
                "      USERID AS username,\n" +
                "      STATUS\n" +
                "    FROM ORDERS, ORDERSTATUS\n" +
                "    WHERE ORDERS.ORDERID = ?\n" +
                "      AND ORDERS.ORDERID = ORDERSTATUS.ORDERID";

        return template.queryForObject(sql,new BeanPropertyRowMapper<Order>(Order.class),orderId);
    }

    @Override
    public void insertOrder(Order order) {
        String sql = " INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE,\n" +
                "      SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY,\n" +
                "      COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME,\n" +
                "      CREDITCARD, EXPRDATE, CARDTYPE, LOCALE)\n" +
                "    VALUES(?, ?, ?, ?, ?, ?,\n" +
                "      ?, ?, ?, ?,?, ?,\n" +
                "      ?, ?, ?, ?, ?, ?, ?,\n" +
                "      ?, ?, ?, ?, ?, ?)";
        template.update(sql,order.getOrderId(),order.getUsername(),order.getOrderDate(),order.getShipAddress1(),order.getShipAddress2(),
                order.getShipCity(),order.getShipState(),order.getShipZip(),order.getShipCountry(),order.getBillAddress1(),
                order.getBillAddress2(),order.getBillCity(),order.getBillState(),order.getBillZip(),order.getBillCountry(),
                order.getCourier(),order.getTotalPrice(),order.getBillToFirstName(),order.getBillToLastName(),order.getShipToFirstName(),
                order.getShipToLastName(),order.getCreditCard(),order.getExpiryDate(),order.getCardType(),order.getLocale());
    }

    @Override
    public void insertOrderStatus(Order order) {
        String sql = "INSERT INTO ORDERSTATUS (ORDERID, LINENUM, TIMESTAMP, STATUS)\n" +
                "    VALUES (?, ?, ?, ?)";
        template.update(sql,order.getOrderId(),order.getOrderId(),order.getOrderDate(),order.getStatus());
    }
}
